﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Collections;

namespace DAO
{
    public class BaoCaoTonDAO
    {
        public static float TinhTonDau(String maSach, DateTime ngayBC)
        {
            int thangtruoc = ngayBC.Month;
            int namtruoc = ngayBC.Year;
            if (ngayBC.Month != 1)
                thangtruoc = ngayBC.Month - 1;
            else
            {
                thangtruoc = 12;
                namtruoc--;
            }

            ArrayList arr = new ArrayList();
            string sql;
            sql = "Select MaSach,TonCuoi,bc.MaBaoCao,bc.ThangBaoCao From BaoCao bc,BaoCaoTon bct" +
                " where bc.MaBaoCao=bct.MaBaoCao and bct.TrangThai=1" +
                " and MONTH(bc.ThangBaoCao)=" + thangtruoc + " and YEAR(bc.ThangBaoCao)=" + namtruoc +
                " and MaSach='" + maSach + "'" +
                " group by MaSach,TonCuoi,bc.MaBaoCao,bc.ThangBaoCao" +
                " order by bc.ThangBaoCao DESC";
            DataTable dt = DataAccess.ExcuQuery(sql);
            if (dt.Rows.Count == 0)
                return 0;
            else
            {
                return float.Parse(dt.Rows[0]["TonCuoi"].ToString());
            }
        }

        public static int ThemBaoCaoTon(BaoCaoTonDTO dv)
        {
            string sql;
            sql = "Insert into BaoCaoTon(MaBaoCaoTon, MaSach,MaBaoCao,TonDau,PhatSinh,TonCuoi,TrangThai,NgayThucHien) values ('" + dv.MaBaoCaoTon + "','" + dv.Sach.MaSach + "','" + dv.BaoCao.MaBaoCao + "'," + dv.TonDau + "," + dv.PhaiSinhTon + "," + dv.TonCuoi + "," + dv.TrangThai + ",'" + dv.NgayThucHien + "')";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }

        public static DataTable LapBaoCaoTon()
        {
            string sql;
            sql = "select top (select count(*) from Sach WHERE trangthai=1) MaSach,TonDau,PhatSinh,TonCuoi from BAOCAOTON" +
                    " group by MaSach,TonDau,PhatSinh,TonCuoi,NgaythucHien" +
                    " order by NgayThucHien DESC";
            return DataAccess.ExcuQuery(sql);
        }
    }
}
